package com.hdu.dwh.utils;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

import com.hdu.dwh.pojos.QueryResult;
import com.hdu.dwh.pojos.source_access_pojos.DataWarehouseDatabase;
import com.hdu.dwh.pojos.source_access_pojos.DataWarehouseTable;
import org.apache.hadoop.thirdparty.org.checkerframework.checker.units.qual.s;
import org.apache.hadoop.yarn.webapp.hamlet.Hamlet.I;
import com.hdu.dwh.service.cube_service.CuboidService;
import com.hdu.dwh.dtos.cube_dtos.CubeDimensionDto;
import com.hdu.dwh.dtos.cube_dtos.CubeDto;
import com.hdu.dwh.dtos.cube_dtos.CuboidDimensionDto;
import com.hdu.dwh.dtos.cube_dtos.CuboidMetricDto;
import com.hdu.dwh.dtos.cube_dtos.FilterDto;
import com.hdu.dwh.enums.Hierachy;
import com.hdu.dwh.pojos.DimensionFilter;
import com.hdu.dwh.pojos.DimensionPivot;
import com.hdu.dwh.pojos.MetricFilter;
import com.hdu.dwh.pojos.SQLBuilder.SQLSelectBuilder;
import com.hdu.dwh.pojos.cube_pojos.Cube;
import com.hdu.dwh.pojos.cube_pojos.CubeDimension;
import com.hdu.dwh.pojos.cube_pojos.CubeMetric;
import com.hdu.dwh.pojos.cube_pojos.Cuboid;
import com.hdu.dwh.pojos.cube_pojos.CuboidDimension;
import com.hdu.dwh.pojos.cube_pojos.Filter;
import com.hdu.dwh.vos.CubeVo;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class CubeUtils {
    public static CubeDto MockCubeDtoForTest() {

        CubeDto cubeDto = new CubeDto()
                .setModelId(34)
                .setName("ssb_lineorder_supplier_test_cube")
                .setDimensions(MockCubeDimensionDtos())
                .setMetrics(MockCuboidMetricDtos());

        return cubeDto;
    }

    private static Set<CubeDimensionDto> MockCubeDimensionDtos() {
        Integer cubeId = 1;
        Set<CubeDimensionDto> mockData = Set.of(
                new CubeDimensionDto("d_year"),
                new CubeDimensionDto("d_month"),
                new CubeDimensionDto("d_day"));

        return mockData;
    }

    public static Set<CuboidDimensionDto> MockCuboidDimensionDtos() {
        Integer cubeId = 1;
        Set<CuboidDimensionDto> mockData = Set.of(
                new CuboidDimensionDto("d_year"),
                new CuboidDimensionDto("d_month"),
                new CuboidDimensionDto("d_day"));

        return mockData;
    }

    public static Set<CuboidMetricDto> MockCuboidMetricDtos() {
        Integer cubeId = 1;
        Set<CuboidMetricDto> mockData = Set.of(
                new CuboidMetricDto("SUM", "lo_ordtotalprice"),
                new CuboidMetricDto("COUNT", "lo_orderkey"));
        return mockData;
    }

    /**
     * 计算维度的幂集
     * 
     * @param originalSet
     * @return Set<Set<CuboidDimensionDto>> {@link Set<{@link CuboidDimensionDto}>}
     */
    public static Set<Set<CuboidDimensionDto>> getDimensionPowerSet(Set<CubeDimension> originalSet) {
        List<CubeDimension> list = new ArrayList<>(originalSet);
        int n = list.size();
        Set<Set<CuboidDimensionDto>> powerSet = new HashSet<>();

        // 幂集应包含所有子集，包括空集和全集
        for (int i = 0; i <= (1 << n) - 1; i++) {
            Set<CuboidDimensionDto> subset = new LinkedHashSet<>();
            for (int j = 0; j < n; j++) {
                if ((i & (1 << j)) != 0) {
                    CuboidDimensionDto dim = new CuboidDimensionDto(list.get(j).getField());
                    subset.add(dim);
                }
            }
            powerSet.add(subset);
        }
        log.info("得到的幂集大小： {}", powerSet.size());
        return powerSet;
    }

    /**
     * 从幂集种取出比当前维度组合少一维的所有组合
     * 
     * @param 一种维度组合
     * @param 所有维度组合的幂集
     * @return
     * 
     */
    public static Set<Cuboid> getPreLevelCuboids(Set<? extends CuboidDimension> dimensionSet,
            Set<Cuboid> cuboids) {

        Set<Cuboid> preLevelCuboids = new HashSet<>();
        for (Cuboid cuboid : cuboids) {
            Set<? extends CuboidDimension> dims = cuboid.getDimensions();
            if (dims.size() == dimensionSet.size() - 1 && dimensionContain(dims, dimensionSet)) {
                preLevelCuboids.add(cuboid);
            }
        }
        return preLevelCuboids;
    }

    private static boolean dimensionContain(Set<? extends CuboidDimension> dims,
            Set<? extends CuboidDimension> dimensionSet) {
        for (CuboidDimension dim : dims) {
            boolean found = false;
            for (CuboidDimension d : dimensionSet) {
                if (dim.getField().equals(d.getField())) {
                    found = true;
                    break;
                }
            }
            if (!found) {
                return false;
            }
        }
        return true;
    }

    /**
     * 从Cuboids幂集种取出比当前维度dimensions组合多一维的所有组合
     * 
     * @param dimensions 一种维度组合
     * @param cuboids    所有维度组合的幂集
     * @return
     */
    public static Set<Cuboid> getNextLevelCuboids(Set<? extends CuboidDimension> dimensions, Set<Cuboid> cuboids) {
        Set<Cuboid> nextLevelCuboids = new HashSet<>();
        for (Cuboid cuboid : cuboids) {
            Set<? extends CuboidDimension> dims = cuboid.getDimensions();
            if (dims.size() == dimensions.size() + 1 && dimensionContain(dimensions, dims)) {
                nextLevelCuboids.add(cuboid);
            }
        }
        return nextLevelCuboids;
    }

    /**
     * @param cuboid
     * @param cube
     * @param dimensionFilters
     * @param pivots
     * @param databaseName
     * @param tableName
     * @param limit
     * @param offset
     * @return
     */
    public static String generateSql(Cuboid cuboid, Cube cube, Set<DimensionFilter> dimensionFilters,
                                     Set<DimensionPivot> pivots, String databaseName, String tableName, Integer limit, Integer offset) {
        SQLSelectBuilder sqlBuilder = new SQLSelectBuilder();
        LinkedHashSet<String> selectFields = new LinkedHashSet<>();
        if (cuboid.getIsStored()) {
            selectFields.add(" * ");
        } else {
            for (CuboidDimension dim : cuboid.getDimensions()) {
                // 使用StatementUtils.quoteTrino对字段名进行转义，防止SQL注入
                selectFields.add(StatementUtils.quoteTrino(dim.getField()));
            }
            for (CubeMetric met : cube.getMetrics()) {
                // 对聚合函数和字段名进行转义
                selectFields.add(met.getAggFunctionName() + "(" + StatementUtils.quoteTrino(met.getField()) + ") AS " +
                        StatementUtils.quoteTrino(met.getAggFunctionName() + "_" + met.getField()));
            }
        }
        for (DimensionPivot pivot : pivots) {
            // 添加空值检查，避免pivot.getDimensionValues()为null时出现空指针异常
            if (pivot.getDimensionValues() != null) {
                for (String val : pivot.getDimensionValues()) {
                    // 对值进行处理，尝试判断是否为数字
                    String formattedValue;

                    try {
                        // 尝试解析为数字，如果成功则不加引号
                        Double.parseDouble(val);
                        formattedValue = val;
                    } catch (NumberFormatException e) {
                        // 如果解析失败，则加单引号并对单引号进行转义
                        formattedValue = "'" + val.replace("'", "''") + "'";
                    }
                    // 对pivot相关字段进行转义
                    String pivotField = String.format("%s(CASE WHEN %s = %s THEN %s END) AS %s",
                            StatementUtils.quoteTrino(pivot.getFunction()),
                            StatementUtils.quoteTrino(pivot.getDimension()),
                            formattedValue,
                            StatementUtils.quoteTrino(pivot.getMetric()),
                            StatementUtils.quoteTrino(String.format("%s_%s_%s", pivot.getFunction(), pivot.getMetric(), val)));
                    selectFields.add(pivotField);
                }
            }
        }
        // 对数据库名和表名进行转义
        sqlBuilder.select(selectFields)
                .from(StatementUtils.quoteTrino(databaseName), StatementUtils.quoteTrino(tableName))
                .where(cube.getFilters())
                .where(dimensionFilters.stream()
                        .map(df -> new Filter(df.getField(), df.getOperator(), df.getValue()))
                        .collect(Collectors.toSet()));
        if (!cuboid.getIsStored()) {
            sqlBuilder.groupBy(
                            cuboid.getDimensions().stream()
                                    .map(dim -> StatementUtils.quoteTrino(dim.getField()))
                                    .collect(Collectors.toSet()))
                    .having(
                            cuboid.getDimensions().stream()
                                    .map(dim -> new MetricFilter("grouping", dim.getField(), "=", "0"))
                                    .collect(Collectors.toSet()));
        }
        sqlBuilder
                .limit(limit)
                .offset(offset);
        return sqlBuilder.build();
    }

    /**
     * 生成透视表SQL查询 旋转操作查询
     * @param cuboid
     * @param cube
     * @param dimensionFilter
     * @param pivots
     * @param pageNum
     * @param pageSize
     * @param cuboidService
     * @return
     */
    public static String generatePivotSQL(Cuboid cuboid, Cube cube, Set<DimensionFilter> dimensionFilter, Set<DimensionPivot> pivots,
                                          Integer pageNum, Integer pageSize, CuboidService cuboidService,
                                          DataWarehouseTable cuboidTable, DataWarehouseDatabase cuboidDatabase) {
        // 处理透视参数，如果dimensionValues为空，则自动获取所有唯一值
        for (DimensionPivot pivot : pivots) {
            if (pivot.getDimensionValues() == null || pivot.getDimensionValues().isEmpty()) {
                try {
                    // 查询所有唯一的维度值，对字段名和表名进行转义
                    String distinctValuesSql = String.format(
                            "SELECT DISTINCT %s FROM %s.%s",
                            StatementUtils.quoteTrino(pivot.getDimension()),
                            StatementUtils.quoteTrino(cuboidDatabase.getDatabaseName()),
                            StatementUtils.quoteTrino(cuboidTable.getTableName())
                    );
                    QueryResult result = cuboidService.executeCuboidSQL(distinctValuesSql);
                    Set<String> distinctValues = new HashSet<>();
                    if (result != null && result.getRows() != null) {
                        for (List<String> row : result.getRows()) {
                            if (row != null && !row.isEmpty() && row.get(0) != null) {
                                distinctValues.add(row.get(0));
                            }
                        }
                    }
                    pivot.setDimensionValues(distinctValues);
                } catch (Exception e) {
                    log.warn("Failed to fetch distinct values for pivot dimension: " + pivot.getDimension(), e);
                    // 如果获取失败，继续使用空集合
                }
            }
        }
        // 构建透视表SQL
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT ");
        // 添加分组维度字段（除了用于透视的维度）
        Set<String> pivotDimensions = pivots.stream()
                .map(DimensionPivot::getDimension)
                .collect(Collectors.toSet());
        List<String> dimensionFields = new ArrayList<>();
        for (CuboidDimension dim : cuboid.getDimensions()) {
            if (!pivotDimensions.contains(dim.getField())) {
                dimensionFields.add(dim.getField());
            }
        }
        // 添加维度字段到SELECT子句，对字段名进行转义
        if (!dimensionFields.isEmpty()) {
            String escapedFields = dimensionFields.stream()
                    .map(StatementUtils::quoteTrino)
                    .collect(Collectors.joining(", "));
            sqlBuilder.append(escapedFields);
        }
        // 添加透视列（使用条件聚合）
        boolean addedPivotColumn = false;
        for (DimensionPivot pivot : pivots) {
            if (pivot.getDimensionValues() != null && !pivot.getDimensionValues().isEmpty()) {
                for (String value : pivot.getDimensionValues()) {
                    // 添加逗号分隔符（如果不是第一个透视列，或者已经有维度字段）
                    if (addedPivotColumn || !dimensionFields.isEmpty()) {
                        sqlBuilder.append(", ");
                    }
                    // 确保列名是有效的标识符
                    String safeValue = value.replaceAll("[^a-zA-Z0-9_]", "_");
                    // 确保别名以字母或下划线开头，避免以数字开头的别名
                    if (!safeValue.isEmpty() && Character.isDigit(safeValue.charAt(0))) {
                        safeValue = "_" + safeValue;
                    }
                    // 从cube_metric表中查找对应的基础字段的聚合函数
                    String actualAggFunction = "COUNT"; // 默认值
                    Set<CubeMetric> cubeMetrics = cube.getMetrics();

                    if (cubeMetrics != null) {
                        for (CubeMetric cubeMetric : cubeMetrics) {
                            // 查找匹配的基础字段名
                            if (cubeMetric.getField() != null && cubeMetric.getField().equals(pivot.getMetric())) {
                                actualAggFunction = cubeMetric.getAggFunctionName();
                                break;
                            }
                        }
                    }

                    // 根据查找到的聚合函数和基础字段名构造实际字段名
                    String actualMetricField = actualAggFunction + "_" + pivot.getMetric();
                    // 对值进行处理，尝试判断是否为数字
                    String formattedValue;
                    try {
                        // 尝试解析为数字，如果成功则不加引号
                        Double.parseDouble(value);
                        formattedValue = value;
                    } catch (NumberFormatException e) {
                        // 如果解析失败，则加单引号并对单引号进行转义
                        formattedValue = "'" + value.replace("'", "''") + "'";
                    }
                    // 对pivot相关字段进行转义
                    String pivotColumn = String.format("%s(CASE WHEN %s = %s THEN %s ELSE 0 END) AS %s",
                            StatementUtils.quoteTrino(pivot.getFunction()),
                            StatementUtils.quoteTrino(pivot.getDimension()),
                            formattedValue,
                            StatementUtils.quoteTrino(actualMetricField),  // 使用从数据库查找到的实际字段名
                            StatementUtils.quoteTrino(String.format("%s_%s_%s", pivot.getFunction(), pivot.getMetric(), safeValue)));
                    sqlBuilder.append(pivotColumn);
                    addedPivotColumn = true;
                }
            }
        }
        // 如果没有添加任何字段，使用默认的*
        if (dimensionFields.isEmpty() && !addedPivotColumn) {
            sqlBuilder.append("*");
        }
        // 添加FROM子句，对数据库名和表名进行转义
        sqlBuilder.append(" FROM %s.%s".formatted(
                StatementUtils.quoteTrino(cuboidDatabase.getDatabaseName()),
                StatementUtils.quoteTrino(cuboidTable.getTableName())));
        // 添加WHERE条件
        if (dimensionFilter != null && !dimensionFilter.isEmpty() && dimensionFilter.size() > 0) {
            sqlBuilder.append(" WHERE ");
            int index = 0;
            for (DimensionFilter filter : dimensionFilter) {
                if (index > 0) {
                    sqlBuilder.append(" AND ");
                }
                // 对字段名进行转义，防止SQL注入
                sqlBuilder.append(String.format("%s %s %s",
                        StatementUtils.quoteTrino(filter.getField()),
                        filter.getOperator(),
                        filter.getValue()));
                index++;
            }
        }
        // 添加GROUP BY子句（如果有维度字段），对字段名进行转义
        if (!dimensionFields.isEmpty()) {
            sqlBuilder.append(" GROUP BY ");
            String escapedGroupFields = dimensionFields.stream()
                    .map(StatementUtils::quoteTrino)
                    .collect(Collectors.joining(", "));
            sqlBuilder.append(escapedGroupFields);
        }
        // 添加ORDER BY子句，确保结果有序，对字段名进行转义
        if (!dimensionFields.isEmpty()) {
            sqlBuilder.append(" ORDER BY ");
            String escapedOrderFields = dimensionFields.stream()
                    .map(StatementUtils::quoteTrino)
                    .collect(Collectors.joining(", "));
            sqlBuilder.append(escapedOrderFields);
        }
        // 添加LIMIT和OFFSET
        if (pageNum != null && pageSize != null) {
            int offset = (pageNum - 1) * pageSize;
            sqlBuilder.append(String.format(" OFFSET %d LIMIT %d", offset, pageSize));
        } else if (pageSize != null) {
            sqlBuilder.append(String.format(" LIMIT %d", pageSize));
        }
        log.info("Generated pivot SQL: {}", sqlBuilder.toString());
        return sqlBuilder.toString();
    }

    public static void copyCubeToCubeVo(Cube cube, CubeVo cubeVo) {
        cubeVo.setId(cube.getId());
        cubeVo.setName(cube.getName());
        cubeVo.setDescription(cube.getDescription());
        cubeVo.setModelId(cube.getModelId());
        cubeVo.setCuboids(cube.getCuboids());
        cubeVo.setMetrics(cube.getMetrics());
    }

    /**
     * CuboidDimensionDtoDTO转换为CuboidDimension
     * 
     * @param dimensions
     * @param cube
     * @return Set<CubeDimension> {@link Set<{@link CubeDimension}>}
     */
    public static Set<CubeDimension> CubeConvertDimensionDtoToDimension(Set<CubeDimensionDto> dimensions, Cube cube) {
        return dimensions.stream()
                .map(dto -> {
                    CubeDimension dim = new CubeDimension(cube.getId(), dto.getField());
                    return dim;
                })
                .collect(Collectors.toSet());
    }

    /**
     * CuboidDimensionDto转换为CuboidDimension
     * 
     * @param dimensions
     * @param cuboid
     * @return Set<CuboidDimension> {@link Set<{@link CuboidDimension}>}
     */
    public static Set<CuboidDimension> CuboidConvertDimensionDtoToDimension(Set<CuboidDimensionDto> dimensions,
            Cuboid cuboid) {
        return dimensions.stream()
                .map(dto -> {
                    CuboidDimension dim = new CuboidDimension(cuboid.getId(), dto.getField());
                    return dim;
                })
                .collect(Collectors.toSet());
    }

    public static Set<CubeMetric> convertMetricDtoToMetric(Set<CuboidMetricDto> metrics, Cube cube) {
        return metrics.stream()
                .map(dto -> {
                    CubeMetric met = new CubeMetric(cube.getId(), dto.getAggFuncName(), dto.getField());
                    return met;
                })
                .collect(Collectors.toSet());
    }

    public static Set<Filter> convertFilterDtoToFilter(Set<FilterDto> filters, Cube cube) {
        return filters.stream()
                .map(dto -> {
                    Filter filter = new Filter(Hierachy.DWS, cube.getId(), dto.getField(), dto.getOperator(),
                            dto.getValue());
                    return filter;
                })
                .collect(Collectors.toSet());
    }
}
